﻿CREATE PROCEDURE CAT.InsertProduct
	@code VARCHAR(20) = NULL,
	@name VARCHAR(50) = NULL,
	@description VARCHAR(200) = NULL,

	@price FLOAT = NULL,
	@dimensions VARCHAR(20) = NULL,

	@id INT = NULL OUTPUT,

	@active BIT = NULL,

	@checkValidation BIT = 1
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300),
		--The result of the procedure. If all goes ok it will be 0, else it will contain the code
		--of the error that has been thrown.
		@result INT,
		--To be used to store the value of the global variable @@ROWCOUNT
		@rowsCount INT


	SELECT
		@procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID),
		@result = 0
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ '@code: ' + ISNULL(@code, '')
		+ ', @name: ' + ISNULL(@name, '')
		+ ', @description: ' + ISNULL(@description, '')
		+ ', @price: ' + ISNULL(CAST(@price AS VARCHAR), '')
		+ ', @active: ' + ISNULL(CAST(@active AS VARCHAR), '')
		+ ', @dimensions: ' + ISNULL(@dimensions, '')
		+ ', @checkValidation: ' + ISNULL(CAST(@checkValidation AS VARCHAR), '')


	-----------------------
	-- Transaction variables
	-----------------------
	DECLARE
		--This variable is used as a checkpoint for handle nested transactions.
		@rollbackPoint NCHAR(32),
		--Indicates if the transaction to be commited is owned by the current procedure.
		@ownTransaction BIT

	SET @ownTransaction = 0
		

	-----------------------
	-- Parameters defaults
	-----------------------
	IF (@checkValidation IS NULL)
		SET @checkValidation = 1


	-----------------------
	-- Parameters validation
	-----------------------
	IF (@checkValidation = 1)
	BEGIN
		IF ((@result = 0) AND (@code IS NULL))
		BEGIN
			RAISERROR('Los argumentos %s no pueden ser nulos o vacíos.', 11, 1, '"Código"')
			SET @result = @@ERROR --Set @result with the current @@Error value.				
		END

		IF (@result = 0)
		BEGIN
			EXEC @result = CAT.ValidateProduct
				@code = @code,
				@price = @price,
				@checkExist = 0
		END
	END


	-----------------------
	-- Begin Transaction
	-----------------------
	IF (@result = 0)
	BEGIN
		PRINT @procName + 'Attempt to insert a product.'

		IF (@@TRANCOUNT > 0)
		BEGIN
			SET @rollbackPoint = REPLACE(CONVERT(NCHAR(36), NEWID()), N'-', N'')
			SAVE TRANSACTION @rollbackPoint
		END
		ELSE
		BEGIN
			SET @ownTransaction = 1
			BEGIN TRANSACTION
		END

		SET @result = @@ERROR --Set @result with the current @@Error value.


		-----------------------
		-- Insert
		-----------------------
		IF (@result = 0)
		BEGIN
			INSERT INTO CAT.Products
			(
				Code
			)
			VALUES
			(
				@code
			)

			SELECT
				@id = @@IDENTITY,
				@result = @@ERROR --Set @result with the current @@Error value.

			IF (@result = 0)
			BEGIN
				PRINT @procName + 'Inserted id "' + CAST(@id AS VARCHAR) + '". Now update its properties.'

				UPDATE CAT.Products
				SET
					Code = CASE WHEN (@code IS NULL) THEN
									Code
								ELSE
									@code
							END,
					Name = CASE WHEN (@name IS NULL) THEN
									Name
								ELSE
									@name
							END,
					[Description] = CASE WHEN (@description IS NULL) THEN
									[Description]
								ELSE
									@description
							END,
					Price = CASE WHEN (@price IS NULL) THEN
									Price
								ELSE
									@price
							END,
					Dimensions = CASE WHEN (@dimensions IS NULL) THEN
									Dimensions
								ELSE
									@dimensions
							END,
					Active = CASE WHEN (@active IS NULL) THEN
									Active
								ELSE
									@active
							END
				WHERE
					Id = @id

				SELECT
					@rowsCount = @@ROWCOUNT,
					@result = @@ERROR --Set @result with the current @@Error value.

				IF ((@result = 0) AND (@rowsCount <> 0))
				BEGIN
					PRINT @procName + 'Update id "' + CAST(@id AS VARCHAR) + '"'
				END
				ELSE
				BEGIN
					RAISERROR('El registro con id "%d" no fue editado.', 11, 1, @id)
					SET @result = @@ERROR --Set @result with the current @@Error value.	
				END
			END
		END


		-----------------------
		-- End Transaction
		-----------------------

		--If @result = 0, attempt to commit the transaction. Else rollback it.
		IF (@result = 0)
		BEGIN
			IF (@ownTransaction = 1)
			BEGIN
				PRINT @procName + 'Commit transaction'
				COMMIT TRANSACTION
			END
		END
		ELSE
		BEGIN
			IF ((@rollbackPoint IS NOT NULL) AND (XACT_STATE() = 1))
			BEGIN
				PRINT @procName + 'Rollback transaction point ' + @rollbackPoint
				ROLLBACK TRANSACTION @rollbackPoint;
			END
			ELSE IF (@ownTransaction = 1)
			BEGIN
				PRINT @procName + 'Rollback transaction'
				ROLLBACK TRANSACTION;
			END
		END
	END


	-----------------------
	-- Finish procedure
	-----------------------
	IF (@result = 0)
		PRINT @procName + @finishEndMSG
	ELSE 
		PRINT @procName + @finishEndMSG + ' Result: ' + CAST(@result AS VARCHAR)

	RETURN @result
END

GO
GRANT EXECUTE
    ON OBJECT::CAT.InsertProduct TO [CatalogEditor]
    AS [dbo];